package com.scuec.trainingmanagementsys.mapper;

import com.scuec.trainingmanagementsys.entity.Project;
import org.apache.ibatis.annotations.*;
import java.util.List;
import java.util.Map;

@Mapper
public interface ProjectMapper {
    // 修改：返回项目ID
    @Insert("INSERT INTO Project (ProjectName, ProjectDescription, TeacherID, StartDate, EndDate, AvailableSlots, ProjectStatus) " +
            "VALUES (#{projectName}, #{projectDescription}, #{teacherId}, #{startDate}, #{endDate}, #{availableSlots}, #{projectStatus})")
    @Options(useGeneratedKeys = true, keyProperty = "projectId")
    int insert(Project project);

    // 根据教师ID查询项目
    @Select("SELECT " +
            "p.ProjectID, " +
            "p.ProjectName, " +
            "p.ProjectDescription, " +
            "p.TeacherID AS teacherId, " +
            "p.StartDate, " +
            "p.EndDate, " +
            "p.AvailableSlots, " +
            "p.ProjectStatus, " +
            "t.TeacherName " +
            "FROM Project p " +
            "LEFT JOIN Teacher t ON p.TeacherID = t.TeacherID " +
            "WHERE p.TeacherID = #{teacherId}")
    List<Project> selectByTeacherId(Integer teacherId);

    // 查询所有项目（左连接教师表获取teacherName）
    @Select("SELECT " +
            "p.ProjectID, " +
            "p.ProjectName, " +
            "p.ProjectDescription, " +
            "p.TeacherID AS teacherId, " +
            "p.StartDate, " +
            "p.EndDate, " +
            "p.AvailableSlots, " +
            "p.ProjectStatus, " +
            "t.TeacherName " + // 新增教师姓名字段
            "FROM Project p " +
            "LEFT JOIN Teacher t ON p.TeacherID = t.TeacherID") // 左连接确保无教师记录时仍显示项目
    List<Project> selectAll();

    // 根据ID查询项目（左连接教师表）
    @Select("SELECT " +
            "p.ProjectID, " +
            "p.ProjectName, " +
            "p.ProjectDescription, " +
            "p.TeacherID AS teacherId, " +
            "p.StartDate, " +
            "p.EndDate, " +
            "p.AvailableSlots, " +
            "p.ProjectStatus, " +
            "t.TeacherName " +
            "FROM Project p " +
            "LEFT JOIN Teacher t ON p.TeacherID = t.TeacherID " +
            "WHERE p.ProjectID = #{projectId}")
    Project selectById(Integer projectId);

    // 删除、更新方法无需修改（仍基于TeacherID操作）
    @Delete("DELETE FROM Project WHERE ProjectID = #{projectId}")
    int deleteById(Integer projectId);

    @Update("UPDATE Project SET " +
            "ProjectName = #{projectName}, " +
            "ProjectDescription = #{projectDescription}, " +
            "TeacherID = #{teacherId}, " +
            "StartDate = #{startDate}, " +
            "EndDate = #{endDate}, " +
            "AvailableSlots = #{availableSlots}, " +
            "ProjectStatus = #{projectStatus} " +
            "WHERE ProjectID = #{projectId}")
    int update(Project project);

    // 获取项目总数
    @Select("SELECT COUNT(*) FROM Project")
    int getProjectCount();

    // 根据项目名称搜索项目
    @Select("SELECT " +
            "p.ProjectID, " +
            "p.ProjectName, " +
            "p.ProjectDescription, " +
            "p.TeacherID AS teacherId, " +
            "p.StartDate, " +
            "p.EndDate, " +
            "p.AvailableSlots, " +
            "p.ProjectStatus, " +
            "t.TeacherName " +
            "FROM Project p " +
            "LEFT JOIN Teacher t ON p.TeacherID = t.TeacherID " +
            "WHERE p.ProjectName LIKE CONCAT('%', #{projectName}, '%')")
    List<Project> selectByProjectName(String projectName);

    // 更新项目状态
    @Update("UPDATE Project SET ProjectStatus = #{status} WHERE ProjectID = #{projectId}")
    int updateProjectStatus(@Param("projectId") Integer projectId, @Param("status") String status);

    // 新增方法：获取项目下所有小组
    @Select("SELECT * FROM Team WHERE ProjectID = #{projectId}")
    List<Map<String, Object>> getTeamsByProjectId(Integer projectId);

    @Delete("<script>" +
            "DELETE FROM project WHERE ProjectID IN " +
            "<foreach item='id' collection='list' open='(' separator=',' close=')'>" +
            "#{id}" +
            "</foreach>" +
            "</script>")
    int batchDelete(List<Integer> ids);
    @Select("SELECT p.projectId, p.projectName, p.teamID FROM project p")
    List<Project> getProjectList();
}